/* *****************************************************************************************************************/
/* ******************* Construct dataset for the analysis of mutual funds� trade performance  *********************/
/* *****************************************************************************************************************/

*** Measure vote alpha at the fund-quarter level; 
data quarterly; set temp.fundret_monthly;
  qtr = intnx('quarter', caldt, 0, 'end');
  format qtr date9.;

proc sort data = quarterly nodupkey; by wficn crsp_cl_grp qtr; 
run;

proc sql;
  create table reg as
  select a.wficn, a.crsp_cl_grp, a.qtr, b.permno, b.meetingdate, b.itemonagendaid, b.issForMgmt, b.car12d_m, b.car12d_m_win,
  b.WinVote, b.margin, b.mgmt_win, b.market_value_pr2, b.market_value_adj, b.voteAgainstMgmt, b.iss_conform
  from quarterly as a, test1 as b
  where a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and 1 <= intck('month', b.meetingdate, a.qtr) <= 12 and b.car12d_m ne .; 
quit;

proc sort data = reg nodupkey; by wficn crsp_cl_grp qtr permno meetingdate itemonagendaid; run;

proc means data = reg noprint; by wficn crsp_cl_grp qtr; 
  var WinVote; 
  output out = count2x (drop=_type_ _freq_) n = num_votes;

proc means data = reg noprint; by wficn crsp_cl_grp qtr; 
  var car12d_m_win; weight market_value_adj; 
  output out = win_ret_1x (drop=_type_ _freq_) mean = vote_alpha;

data measure1; merge count2x win_ret_1x; by wficn crsp_cl_grp qtr;

data fundinfo; set temp.fundinfo;
  if mtna ne .; 

proc sql;
  create table measure1 as
  select a.*, b.*
  from measure1 as a, fundinfo as b
  where a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and intck('quarter', b.caldt, a.qtr) = 0; *;
quit;

proc sort data = reg; by wficn crsp_cl_grp qtr;

proc means noprint data = reg; by wficn crsp_cl_grp qtr;
  var voteAgainstMgmt iss_conform;
  output out = iss_mgmt_qtr (drop=_type_ _freq_) mean = voteAgainstMgmt iss_conform;
run; 

proc sql;
  create table measure1 as
  select a.*, b.*
  from measure1 as a left join iss_mgmt_qtr as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and intck('quarter', b.qtr, a.qtr) = 0;
quit;

proc sql;
  create table measure1 as
  select a.*, b.vote_alpha as vote_alpha_lag
  from measure1 as a left join measure1 as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and intck('quarter', b.qtr, a.qtr) = 1; * make sure the fund voted in the previous quarter;
quit;

proc sql;
  create table measure1 as
  select a.*, b.flow_style_adj as flow_style_adj_pr12
  from measure1 as a left join temp.flow_qtrly as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and intck('quarter', b.qtr, a.qtr) = 0;
quit;

proc sql;
  create table measure1 as
  select a.*, b.mret as mret_pr12, b.mret_style_adj as mret_style_adj_pr12
  from measure1 as a left join temp.fundret_qtr_annual as b
  on a.wficn = b.wficn and a.crsp_cl_grp = b.crsp_cl_grp and intck('quarter', b.qtr, a.qtr) = 0;
quit;

data measure1b; set measure1;
  if index(crsp_obj_cd2, 'ED') = 1 and num_votes > 10 and mtna >= 5 and vote_alpha_lag > .; 
run;

proc sort nodupkey data = measure1b; by qtr wficn crsp_cl_grp;

proc rank data = measure1b out  = measure1b groups = 5; by qtr;
  var vote_alpha;
  ranks q_vote_alpha;

data temp.measure_fund_qtr; set measure1b;
  if q_vote_alpha = 4 then informed = 1; else informed = 0;
  if '30jun2004'd <= qtr <= '30jun2018'd;
run;

*** Merge with trades;
proc sql;
  create table trades as
  select a.wficn, a.qtr, a.crsp_cl_grp, a.q_vote_alpha, a.mtna, b.permno, b.rqdate, b.rdate, b.rdate_last, b.pwt, b.pwt_last, b.market_value, b.market_value_last, b.netbuy
  from temp.measure_fund_qtr as a left join temp.s12_trades as b
  on a.wficn = b.wficn and intck('quarter', a.qtr, b.rqdate) = 1;
quit; * temp.measure_fund_qtr is from 07Calendar_time_portfolio_fund_quarterly.sas;

data no_s12; set trades;
  if rdate = .;
  drop permno rqdate rdate rdate_last pwt pwt_last market_value market_value_last netbuy;

proc sort nodupkey; by wficn crsp_cl_grp qtr; 

data s12_trades; set trades;
  if rdate ne .;

proc sql;
  create table trades as
  select a.wficn, a.qtr, a.crsp_cl_grp, a.q_vote_alpha, a.mtna, b.permno, b.rqdate, b.rdate, b.rdate_last, b.pwt, b.pwt_last, b.market_value, b.market_value_last, b.netbuy
  from no_s12 as a left join temp.trades as b
  on a.crsp_cl_grp = b.crsp_cl_grp and intck('quarter', a.qtr, b.rqdate) = 1;
quit;  * temp.trades is from 16Netbuy_Postvote.sas;
 
data crsp_trades; set trades;
  if rdate ne .;

data all_trades; set s12_trades crsp_trades;
run;


*** Compute future stock returns (up to 18 months);
proc sort data = all_trades nodupkey out = permno_rdate (keep=permno rdate); by permno rdate;

proc sql;
  create table permno_rdate_ret as
  select a.*, b.ret, log(1+b.ret) as log_ret, b.date
  from permno_rdate as a, crsp.msf as b
  where a.permno = b.permno and 1 <= intck('month', a.rdate, b.date) <= 18;
quit;

proc sql;
  create table permno_rdate_ret
  as select a.*, b.vwretd, a.ret-b.vwretd as xret
  from permno_rdate_ret as a, crsp.msi as b
  where a.date = b.date;
quit;

proc sql;
  create table permno_rdate_ret as
  select a.*, b.dgtw_xret, log(1+b.dgtw_xret) as log_ret_dgtw 
  from permno_rdate_ret as a, temp.dgtw_returns as b
  where a.permno = b.permno and year(a.date) = year(b.date) and month(a.date) = month(b.date);
quit;

proc sort data = permno_rdate_ret; by permno rdate;

proc means data = permno_rdate_ret noprint; by permno rdate;
  var xret log_ret log_ret_dgtw; 
  output out = xret6q (drop=_type_ _freq_) sum = ret_mktadj6q clog_ret6q clog_ret_dgtw6q; 

proc means data = permno_rdate_ret noprint; by permno rdate;
  var xret log_ret log_ret_dgtw;  where intck('month', rdate, date) <= 12; 
  output out = xret4q (drop=_type_ _freq_) sum = ret_mktadj4q clog_ret4q clog_ret_dgtw4q; 

proc means data = permno_rdate_ret noprint; by permno rdate;
  var xret log_ret log_ret_dgtw;  where intck('month', rdate, date) <= 6; 
  output out = xret2q (drop=_type_ _freq_) sum = ret_mktadj2q clog_ret2q clog_ret_dgtw2q; 

proc means data = permno_rdate_ret noprint; by permno rdate;
  var xret log_ret log_ret_dgtw;  where intck('month', rdate, date) <= 3; 
  output out = xret1q (drop=_type_ _freq_) sum = ret_mktadj clog_ret clog_ret_dgtw; 

data xret; merge xret6q xret4q xret2q xret1q; by permno rdate;
  ret = exp(clog_ret)-1;
  ret_dgtw = exp(clog_ret_dgtw)-1;
  ret2q = exp(clog_ret2q)-1;
  ret_dgtw2q = exp(clog_ret_dgtw2q)-1;
  ret4q = exp(clog_ret4q)-1;
  ret_dgtw4q = exp(clog_ret_dgtw4q)-1;
  ret6q = exp(clog_ret6q)-1;
  ret_dgtw6q = exp(clog_ret_dgtw6q)-1;
  drop clog_ret clog_ret_dgtw clog_ret2q clog_ret_dgtw2q clog_ret4q clog_ret_dgtw4q clog_ret6q clog_ret_dgtw6q;
  if ret ne .;
run;

*** Adjust the portfolio weights in q-1;
proc sort data = all_trades nodupkey out = permno_rdate2 (keep=permno rdate rdate_last); by permno rdate rdate_last;
  where permno ne . and rdate_last ne .;

proc sql;
  create table permno_rdate_ret2 as
  select a.*, log(1+b.ret) as log_ret, b.date
  from permno_rdate2 as a, crsp.msf as b
  where a.permno = b.permno and intnx('month', a.rdate_last, 0, 'end') < intnx('month', b.date, 0, 'end') <= intnx('month', a.rdate, 0, 'end');
quit;

proc sort data = permno_rdate_ret2; by permno rdate rdate_last;

proc means data = permno_rdate_ret2 noprint; by permno rdate rdate_last;
  var log_ret; 
  output out = adj_pwt (drop=_type_ _freq_) sum = clog_ret;

data adj_pwt; set adj_pwt;
  adj_factor = exp(clog_ret);
  drop clog_ret;

*** Add the returns and adjustment factors;
proc sql;
  create table all_trades1 as
  select a.*, b.*
  from all_trades as a left join adj_pwt as b
  on a.permno = b.permno and a.rdate = b.rdate and a.rdate_last = b.rdate_last;
quit;

data all_trades1; set all_trades1;
  market_value_last_adj = market_value_last*adj_factor;
  if market_value ne . and market_value_last_adj ne .; 
  if q_vote_alpha = 4 then informed = 1; else informed = 0; 
run;

proc sort data = all_trades1; by qtr informed wficn crsp_cl_grp permno;

proc means noprint data = all_trades1; by qtr informed wficn crsp_cl_grp;
  var market_value market_value_last_adj;
  output out = q_port_sum (drop=_type_ _freq_) sum = market_value_sum market_value_last_adj_sum n = num_stocks;

data all_trades2; merge all_trades1 q_port_sum; by qtr informed wficn crsp_cl_grp;
  if market_value_sum > 0 then pwt = market_value/market_value_sum;
  if market_value_last_adj_sum > 0 then pwt_last_pseudo = market_value_last_adj/market_value_last_adj_sum;
  delta_pwt = pwt-pwt_last_pseudo;

proc means noprint data = all_trades2; by qtr informed wficn crsp_cl_grp;
  var pwt pwt_last_pseudo;
  output out = x (drop=_type_ _freq_) sum = pwt pwt_last_pseudo; run;

proc means data = all_trades2 n mean median min max p1 p99 p25 p75; class informed; 
  var delta_pwt;
run;

proc sort data = all_trades2; by qtr informed wficn crsp_cl_grp;

proc means noprint data = all_trades2; by qtr informed wficn crsp_cl_grp;
  var delta_pwt; where delta_pwt > 0;
  output out = buy_weight_sum (drop=_type_ _freq_) sum = delta_pwt_buy_sum;

proc means noprint data = all_trades2; by qtr informed wficn crsp_cl_grp;
  var delta_pwt; where delta_pwt < 0 and delta_pwt ne .;
  output out = sell_weight_sum (drop=_type_ _freq_) sum = delta_pwt_sell_sum;

data all_trades2; merge all_trades2 buy_weight_sum sell_weight_sum; by qtr informed wficn crsp_cl_grp;
  if delta_pwt > 0 then wt = delta_pwt/delta_pwt_buy_sum;
  if delta_pwt < 0 and delta_pwt ne . then wt = delta_pwt/delta_pwt_sell_sum; 

proc sql;
  create table all_trades2 as
  select a.*, b.*
  from all_trades2 as a, xret as b
  where a.permno = b.permno and intck('month', a.rdate, b.rdate) = 0;
quit;

proc sort data = all_trades2; by qtr informed wficn crsp_cl_grp;

proc means noprint data = all_trades2; by qtr informed wficn crsp_cl_grp;
  var ret ret_dgtw ret2q ret_dgtw2q ret4q ret_dgtw4q ret6q ret_dgtw6q; where delta_pwt > 0; weight wt;
  output out = buy (drop=_type_ _freq_) mean = ret_delta_pwt_buy ret_dgtw_delta_pwt_buy ret2q_delta_pwt_buy ret_dgtw2q_delta_pwt_buy 
    ret4q_delta_pwt_buy ret_dgtw4q_delta_pwt_buy ret6q_delta_pwt_buy ret_dgtw6q_delta_pwt_buy;

proc means noprint data = all_trades2; by qtr informed wficn crsp_cl_grp;
  var ret ret_dgtw ret2q ret_dgtw2q ret4q ret_dgtw4q ret6q ret_dgtw6q; where delta_pwt < 0 and delta_pwt ne .; weight wt;
  output out = sell (drop=_type_ _freq_) mean = ret_delta_pwt_sell ret_dgtw_delta_pwt_sell ret2q_delta_pwt_sell ret_dgtw2q_delta_pwt_sell 
    ret4q_delta_pwt_sell ret_dgtw4q_delta_pwt_sell ret6q_delta_pwt_sell ret_dgtw6q_delta_pwt_sell;

data trade_performance; merge buy sell; by qtr informed wficn crsp_cl_grp;
  trade_perf_raw = ret_delta_pwt_buy-ret_delta_pwt_sell;
  trade_perf_dgtw = ret_dgtw_delta_pwt_buy-ret_dgtw_delta_pwt_sell;
  trade_perf_raw2q = ret2q_delta_pwt_buy-ret2q_delta_pwt_sell;
  trade_perf_dgtw2q = ret_dgtw2q_delta_pwt_buy-ret_dgtw2q_delta_pwt_sell;
  trade_perf_raw4q = ret4q_delta_pwt_buy-ret4q_delta_pwt_sell;
  trade_perf_dgtw4q = ret_dgtw4q_delta_pwt_buy-ret_dgtw4q_delta_pwt_sell;
  trade_perf_raw6q = ret6q_delta_pwt_buy-ret6q_delta_pwt_sell;
  trade_perf_dgtw6q = ret_dgtw6q_delta_pwt_buy-ret_dgtw6q_delta_pwt_sell;
  trade_perf_raw = trade_perf_raw*100; * in percentage points;
  trade_perf_raw2q = trade_perf_raw2q*100; * in percentage points;
  trade_perf_raw4q = trade_perf_raw4q*100; * in percentage points;
  trade_perf_raw6q = trade_perf_raw6q*100; * in percentage points;

%winsor(dsetin=trade_performance, dsetout=trade_performance, byvar=none, 
  vars=trade_perf_raw trade_perf_raw2q  trade_perf_raw4q  trade_perf_raw6q, type=winsor, pctl=.1 99.9);

proc means data = trade_performance n mean t probt; class informed ;
  var trade_perf_raw trade_perf_raw2q  trade_perf_raw4q  trade_perf_raw6q; 
run;

proc ttest data = trade_performance; class informed; where informed in (0, 1);
  var trade_perf_raw trade_perf_raw2q  trade_perf_raw4q  trade_perf_raw6q; 
run;

proc export data= trade_performance 
            outfile= "D:\Dropbox\InformedVoting\trade_performance.dta" 
            dbms=stata replace;
run;




*** Partition the trades based on whether they are placed around a shareholder meeting;
proc sort data = temp.iss_votes_ret nodupkey out = proposals (keep=exchcd shrcd permno meetingdate mgmt_sponsored mgmt_win margin Itemdesc issAgendaItemId itemonagendaid mgmtFor issForMgmt issue); 
  by permno meetingdate itemonagendaid; 

data proposals; set proposals;
  meeting_months = 1;

proc sort nodupkey; by permno meetingdate; 
run;

proc sql;
  create table all_trades1x as
  select a.*, b.meeting_months
  from all_trades1 as a left join proposals as b
  on a.permno = b.permno and a.qtr <= b.meetingdate <= a.rqdate; 
quit;

proc sort nodupkey; by _All_; 

data all_trades1x; set all_trades1x;
  if meeting_months = . then meeting_months = 0;

proc freq data = all_trades1x; tables meeting_months; run;

proc sort; by qtr informed wficn crsp_cl_grp meeting_months permno;

proc means noprint data = all_trades1x; by qtr informed wficn crsp_cl_grp meeting_months;
  var market_value market_value_last_adj;
  output out = q_port_sum (drop=_type_ _freq_) sum = market_value_sum market_value_last_adj_sum;

data all_trades2x; merge all_trades1x q_port_sum; by qtr informed wficn crsp_cl_grp meeting_months;
  if market_value_sum > 0 then pwt = market_value/market_value_sum;
  if market_value_last_adj_sum > 0 then pwt_last_pseudo = market_value_last_adj/market_value_last_adj_sum;
  delta_pwt = pwt-pwt_last_pseudo;

proc freq data = all_trades2x; tables informed; run;

proc sort data = all_trades2x; by qtr informed wficn crsp_cl_grp meeting_months;

proc means noprint data = all_trades2x; by qtr informed wficn crsp_cl_grp meeting_months;
  var delta_pwt; where delta_pwt > 0;
  output out = buy_weight_sum (drop=_type_ _freq_) sum = delta_pwt_buy_sum;

proc means noprint data = all_trades2x; by qtr informed wficn crsp_cl_grp meeting_months;
  var delta_pwt; where delta_pwt < 0 and delta_pwt ne .;
  output out = sell_weight_sum (drop=_type_ _freq_) sum = delta_pwt_sell_sum;

data all_trades2z; merge all_trades2x buy_weight_sum sell_weight_sum; by qtr informed wficn crsp_cl_grp meeting_months;
  if delta_pwt > 0 then wt = delta_pwt/delta_pwt_buy_sum;
  if delta_pwt < 0 and delta_pwt ne . then wt = delta_pwt/delta_pwt_sell_sum;
run;

proc sql;
  create table all_trades2z as
  select a.*, b.*
  from all_trades2z as a, xret as b
  where a.permno = b.permno and intck('month', a.rdate, b.rdate) = 0;
quit;

proc sort data = all_trades2z; by qtr informed wficn crsp_cl_grp meeting_months;

proc means noprint data = all_trades2z; by qtr informed wficn crsp_cl_grp meeting_months;
  var ret ret_dgtw ret2q ret_dgtw2q ret4q ret_dgtw4q ret6q ret_dgtw6q; where delta_pwt > 0; weight wt;
  output out = buy2 (drop=_type_ _freq_) mean = ret_delta_pwt_buy ret_dgtw_delta_pwt_buy ret2q_delta_pwt_buy ret_dgtw2q_delta_pwt_buy 
    ret4q_delta_pwt_buy ret_dgtw4q_delta_pwt_buy ret6q_delta_pwt_buy ret_dgtw6q_delta_pwt_buy;

proc means noprint data = all_trades2z; by qtr informed wficn crsp_cl_grp meeting_months;
  var ret ret_dgtw ret2q ret_dgtw2q ret4q ret_dgtw4q ret6q ret_dgtw6q; where delta_pwt < 0 and delta_pwt ne .; weight wt;
  output out = sell2 (drop=_type_ _freq_) mean = ret_delta_pwt_sell ret_dgtw_delta_pwt_sell ret2q_delta_pwt_sell ret_dgtw2q_delta_pwt_sell 
    ret4q_delta_pwt_sell ret_dgtw4q_delta_pwt_sell ret6q_delta_pwt_sell ret_dgtw6q_delta_pwt_sell;

data trade_performance_splits; merge buy2 sell2; by qtr informed wficn crsp_cl_grp meeting_months;
  trade_perf_raw = ret_delta_pwt_buy-ret_delta_pwt_sell;
  trade_perf_dgtw = ret_dgtw_delta_pwt_buy-ret_dgtw_delta_pwt_sell;
  trade_perf_raw2q = ret2q_delta_pwt_buy-ret2q_delta_pwt_sell;
  trade_perf_dgtw2q = ret_dgtw2q_delta_pwt_buy-ret_dgtw2q_delta_pwt_sell;
  trade_perf_raw4q = ret4q_delta_pwt_buy-ret4q_delta_pwt_sell;
  trade_perf_dgtw4q = ret_dgtw4q_delta_pwt_buy-ret_dgtw4q_delta_pwt_sell;
  trade_perf_raw6q = ret6q_delta_pwt_buy-ret6q_delta_pwt_sell;
  trade_perf_dgtw6q = ret_dgtw6q_delta_pwt_buy-ret_dgtw6q_delta_pwt_sell;

  trade_perf_raw = trade_perf_raw*100; * in percentage points;
  trade_perf_raw2q = trade_perf_raw2q*100; * in percentage points;
  trade_perf_raw4q = trade_perf_raw4q*100; * in percentage points;
  trade_perf_raw6q = trade_perf_raw6q*100; * in percentage points;

%winsor(dsetin=trade_performance_splits, dsetout=trade_performance_splits, byvar=none, 
  vars=trade_perf_raw trade_perf_raw2q trade_perf_raw4q trade_perf_raw6q, type=winsor, pctl=.1 99.9);

proc export data= trade_performance_splits 
            outfile= "D:\Dropbox\InformedVoting\trade_performance_splits.dta" 
            dbms=stata replace;
run;

proc means data = trade_performance_splits n mean t probt; class meeting_months informed;
  var trade_perf_raw trade_perf_raw2q  trade_perf_raw4q  trade_perf_raw6q; 
run;

proc ttest data = trade_performance_splits; class informed ; where meeting_months = 1 and informed in (0,1);
  var trade_perf_raw trade_perf_raw2q  trade_perf_raw4q trade_perf_raw6q; 
run;

proc ttest data = trade_performance_splits; class informed ; where meeting_months = 0 and informed in (0,1);
  var trade_perf_raw trade_perf_raw2q  trade_perf_raw4q trade_perf_raw6q; 
run;




proc means data = trade_performance_splits n mean t probt; class meeting_months informed;
  var trade_perf_dgtw trade_perf_dgtw2q trade_perf_dgtw4q trade_perf_dgtw6q; 
run;

proc ttest data = trade_performance_splits; class informed; where informed in (0, 1);
  var trade_perf_dgtw trade_perf_dgtw2q trade_perf_dgtw4q trade_perf_dgtw6q; 
run;
